---
title: "MySQL Snippets"
date: 2020-6-4
categories:
- mysql
tags:
---

<div id="content">
<div id="table-of-contents">
<h2>Table of Contents</h2>
<div id="text-table-of-contents">
<ul>
<li><a href="#orgdaaf3a2">MySQL Users</a>
<ul>
<li><a href="#org36a7879">Create User</a></li>
</ul>
</li>
<li><a href="#org57f6188">DDL</a>
<ul>
<li><a href="#org208860c">显示建表语句</a></li>
<li><a href="#orgf1006e0">显示所有外键</a></li>
<li><a href="#org3ef14e6">Rename Table</a></li>
<li><a href="#org4f27063">Rename database</a></li>
<li><a href="#orgc65be23">显示表结构</a></li>
<li><a href="#org4743c23">Convert table character set</a></li>
<li><a href="#orgda16d55">获取数据库大小</a></li>
</ul>
</li>
<li><a href="#orgc6547f1">代码</a>
<ul>
<li><a href="#org5e7ed9f">Row Number</a></li>
</ul>
</li>
</ul>
</div>
</div>
<div class="outline-2" id="outline-container-orgdaaf3a2">
<h2 id="orgdaaf3a2">MySQL Users</h2>
<div class="outline-text-2" id="text-orgdaaf3a2">
</div>
<div class="outline-3" id="outline-container-org36a7879">
<h3 id="org36a7879">Create User</h3>
<div class="outline-text-3" id="text-org36a7879">
<div class="org-src-container">
<pre class="src src-sql"><span style="font-weight: bold;">create</span> <span style="font-weight: bold;">user</span> user_name;
<span style="font-weight: bold;">grant</span> <span style="font-weight: bold;">all</span> <span style="font-weight: bold;">privileges</span> <span style="font-weight: bold;">on</span> *.* <span style="font-weight: bold;">to</span> user_name@<span style="font-style: italic;">'%'</span> identified <span style="font-weight: bold;">by</span> <span style="font-style: italic;">'password'</span> <span style="font-weight: bold;">with</span> <span style="font-weight: bold;">grant</span> <span style="font-weight: bold;">option</span>;
flush <span style="font-weight: bold;">privileges</span>;
</pre>
</div>
</div>
</div>
</div>
<div class="outline-2" id="outline-container-org57f6188">
<h2 id="org57f6188">DDL</h2>
<div class="outline-text-2" id="text-org57f6188">
</div>
<div class="outline-3" id="outline-container-org208860c">
<h3 id="org208860c">显示建表语句</h3>
<div class="outline-text-3" id="text-org208860c">
<div class="org-src-container">
<pre class="src src-sql">show <span style="font-weight: bold;">create</span> <span style="font-weight: bold;">table</span> &lt;<span style="font-weight: bold;">table_name</span>&gt;
</pre>
</div>
</div>
</div>
<div class="outline-3" id="outline-container-orgf1006e0">
<h3 id="orgf1006e0">显示所有外键</h3>
<div class="outline-text-3" id="text-orgf1006e0">
<div class="org-src-container">
<pre class="src src-sql"><span style="font-weight: bold;">SELECT</span> 
  <span style="font-weight: bold;">TABLE_NAME</span>,<span style="font-weight: bold;">COLUMN_NAME</span>,<span style="font-weight: bold;">CONSTRAINT_NAME</span>, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
<span style="font-weight: bold;">FROM</span>
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE
<span style="font-weight: bold;">WHERE</span>
  REFERENCED_TABLE_SCHEMA = <span style="font-style: italic;">'&lt;database&gt;'</span> <span style="font-weight: bold;">AND</span>
  REFERENCED_TABLE_NAME = <span style="font-style: italic;">'&lt;table&gt;'</span> <span style="font-weight: bold;">AND</span>
  REFERENCED_COLUMN_NAME = <span style="font-style: italic;">'&lt;column&gt;'</span>;
</pre>
</div>
</div>
</div>
<div class="outline-3" id="outline-container-org3ef14e6">
<h3 id="org3ef14e6">Rename Table</h3>
<div class="outline-text-3" id="text-org3ef14e6">
<div class="org-src-container">
<pre class="src src-sql">RENAME <span style="font-weight: bold;">TABLE</span> old_db.<span style="font-weight: bold;">table</span> <span style="font-weight: bold;">TO</span> new_db.<span style="font-weight: bold;">table</span>;
</pre>
</div>
</div>
</div>
<div class="outline-3" id="outline-container-org4f27063">
<h3 id="org4f27063">Rename database</h3>
<div class="outline-text-3" id="text-org4f27063">
<blockquote>
<p>
<a href="https://stackoverflow.com/questions/67093/how-do-i-quickly-rename-a-mysql-database-change-schema-name">https://stackoverflow.com/questions/67093/how-do-i-quickly-rename-a-mysql-database-change-schema-name</a>
</p>
</blockquote>
<ol class="org-ol">
<li>{% raw %} RENAME {DATABASE | SCHEMA} db_name TO new_db_name; {% endraw %} 有丢失数据的风险。</li>
<li>For InnoDB, {% raw %} RENAME TABLE old_db.table TO new_db.table; {% endraw %} ，将表移到另一个数据库中。通过脚本自动化这个操作：</li>
</ol>
<div class="org-src-container">
<pre class="src src-shell"><span style="font-weight: bold;">for</span> table<span style="font-weight: bold;"> in</span> <span style="font-weight: bold;">`mysql -u root -ppassword -s -N -e "use old_db;show tables from old_db;"`</span>; <span style="font-weight: bold;">do</span> 
    mysql -u root -ppassword -s -N -e <span style="font-style: italic;">"use old_db;rename table old_db.$table to new_db.$table;"</span>; 
<span style="font-weight: bold;">done</span>;
</pre>
</div>
</div>
</div>
<div class="outline-3" id="outline-container-orgc65be23">
<h3 id="orgc65be23">显示表结构</h3>
<div class="outline-text-3" id="text-orgc65be23">
<div class="org-src-container">
<pre class="src src-sql"># 显示表的所有字段
<span style="font-weight: bold;">describe</span> &lt;<span style="font-weight: bold;">table</span> <span style="font-weight: bold;">name</span>&gt;
# 显示表的所有字段和字符集等信息
SHOW <span style="font-weight: bold;">FULL</span> COLUMNS <span style="font-weight: bold;">FROM</span> <span style="font-weight: bold;">table_name</span>;
</pre>
</div>
</div>
</div>
<div class="outline-3" id="outline-container-org4743c23">
<h3 id="org4743c23">Convert table character set</h3>
<div class="outline-text-3" id="text-org4743c23">
<blockquote>
<p>
<a href="https://stackoverflow.com/questions/1294117/how-to-change-collation-of-database-table-column">https://stackoverflow.com/questions/1294117/how-to-change-collation-of-database-table-column</a>
</p>
</blockquote>
<p>
convert 同时也会更改所有列的字符集
</p>
<div class="org-src-container">
<pre class="src src-sql"><span style="font-weight: bold;">ALTER</span> <span style="font-weight: bold;">TABLE</span> <span style="font-weight: bold;">mytable</span> <span style="font-weight: bold;">CONVERT</span> <span style="font-weight: bold;">TO</span> <span style="font-weight: bold; text-decoration: underline;">CHARACTER</span> <span style="font-weight: bold;">SET</span> utf8mb4 
<span style="font-weight: bold;">ALTER</span> <span style="font-weight: bold;">TABLE</span> <span style="font-weight: bold;">MYTABLE</span> <span style="font-weight: bold;">CONVERT</span> <span style="font-weight: bold;">TO</span> <span style="font-weight: bold; text-decoration: underline;">CHARACTER</span> <span style="font-weight: bold;">SET</span> utf8mb4 <span style="font-weight: bold;">COLLATE</span> utf8mb4_unicode_ci;
</pre>
</div>
<p>
设置数据库默认的字符集，但是不会影响现有的表
</p>
<pre class="example">
ALTER SCHEMA database DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
</pre>
</div>
</div>
<div class="outline-3" id="outline-container-orgda16d55">
<h3 id="orgda16d55">获取数据库大小</h3>
<div class="outline-text-3" id="text-orgda16d55">
<blockquote>
<p>
<a href="https://database.guide/how-to-check-the-size-of-a-database-in-mysql/">https://database.guide/how-to-check-the-size-of-a-database-in-mysql/</a>
</p>
</blockquote>
<div class="org-src-container">
<pre class="src src-sql"><span style="font-weight: bold;">SELECT</span> 
    table_schema <span style="font-style: italic;">'Database Name'</span>,
    <span style="font-weight: bold;">SUM</span>(data_length + index_length) <span style="font-style: italic;">'Size in Bytes'</span>,
    ROUND(<span style="font-weight: bold;">SUM</span>(data_length + index_length) / 1024 / 1024, 2) <span style="font-style: italic;">'Size in MiB'</span>
<span style="font-weight: bold;">FROM</span> information_schema.tables 
<span style="font-weight: bold;">GROUP</span> <span style="font-weight: bold;">BY</span> table_schema;
</pre>
</div>
<p>
Result:
</p>
<table border="2" cellpadding="6" cellspacing="0" frame="hsides" rules="groups">
<colgroup>
<col class="org-left"/>
<col class="org-right"/>
<col class="org-right"/>
</colgroup>
<thead>
<tr>
<th class="org-left" scope="col">Database Name</th>
<th class="org-right" scope="col">Size in Bytes</th>
<th class="org-right" scope="col">Size in MiB</th>
</tr>
</thead>
<tbody>
<tr>
<td class="org-left">information_schema</td>
<td class="org-right">0</td>
<td class="org-right">0.00</td>
</tr>
<tr>
<td class="org-left">Music</td>
<td class="org-right">98304</td>
<td class="org-right">0.09</td>
</tr>
<tr>
<td class="org-left">mysql</td>
<td class="org-right">2506752</td>
<td class="org-right">2.39</td>
</tr>
<tr>
<td class="org-left">performance_schema</td>
<td class="org-right">0</td>
<td class="org-right">0.00</td>
</tr>
<tr>
<td class="org-left">sakila</td>
<td class="org-right">6766592</td>
<td class="org-right">6.45</td>
</tr>
<tr>
<td class="org-left">Solutions</td>
<td class="org-right">16384</td>
<td class="org-right">0.02</td>
</tr>
<tr>
<td class="org-left">sys</td>
<td class="org-right">16384</td>
<td class="org-right">0.02</td>
</tr>
<tr>
<td class="org-left">world</td>
<td class="org-right">802816</td>
<td class="org-right">0.77</td>
</tr>
</tbody>
</table>
<div class="org-src-container">
<pre class="src src-sql">USE Music;
<span style="font-weight: bold;">SELECT</span> 
    table_schema <span style="font-style: italic;">'Database Name'</span>,
    <span style="font-weight: bold;">SUM</span>(data_length + index_length) <span style="font-style: italic;">'Size in Bytes'</span>,
    sys.FORMAT_BYTES(<span style="font-weight: bold;">SUM</span>(data_length + index_length)) <span style="font-style: italic;">'Size (Formatted)'</span>
<span style="font-weight: bold;">FROM</span> information_schema.tables 
<span style="font-weight: bold;">GROUP</span> <span style="font-weight: bold;">BY</span> table_schema;
</pre>
</div>
<p>
Result:
</p>
<table border="2" cellpadding="6" cellspacing="0" frame="hsides" rules="groups">
<colgroup>
<col class="org-left"/>
<col class="org-right"/>
<col class="org-left"/>
</colgroup>
<thead>
<tr>
<th class="org-left" scope="col">Database Name</th>
<th class="org-right" scope="col">Size in Bytes</th>
<th class="org-left" scope="col">Size (Formatted)</th>
</tr>
</thead>
<tbody>
<tr>
<td class="org-left">information_schema</td>
<td class="org-right">0</td>
<td class="org-left">0 bytes</td>
</tr>
<tr>
<td class="org-left">Music</td>
<td class="org-right">98304</td>
<td class="org-left">96.00 KiB</td>
</tr>
<tr>
<td class="org-left">mysql</td>
<td class="org-right">2506752</td>
<td class="org-left">2.39 MiB</td>
</tr>
<tr>
<td class="org-left">performance_schema</td>
<td class="org-right">0</td>
<td class="org-left">0 bytes</td>
</tr>
<tr>
<td class="org-left">sakila</td>
<td class="org-right">6766592</td>
<td class="org-left">6.45 MiB</td>
</tr>
<tr>
<td class="org-left">Solutions</td>
<td class="org-right">16384</td>
<td class="org-left">16.00 KiB</td>
</tr>
<tr>
<td class="org-left">sys</td>
<td class="org-right">16384</td>
<td class="org-left">16.00 KiB</td>
</tr>
<tr>
<td class="org-left">world</td>
<td class="org-right">802816</td>
<td class="org-left">784.00 KiB</td>
</tr>
</tbody>
</table>
</div>
</div>
</div>
<div class="outline-2" id="outline-container-orgc6547f1">
<h2 id="orgc6547f1">代码</h2>
<div class="outline-text-2" id="text-orgc6547f1">
</div>
<div class="outline-3" id="outline-container-org5e7ed9f">
<h3 id="org5e7ed9f">Row Number</h3>
<div class="outline-text-3" id="text-org5e7ed9f">
<div class="org-src-container">
<pre class="src src-sql"><span style="font-weight: bold;">SET</span> @row_number = 0;
<span style="font-weight: bold;">SELECT</span>
    (@row_number:=@row_number + 1) <span style="font-weight: bold;">AS</span> `row_number`, ...
<span style="font-weight: bold;">FROM</span> ...
</pre>
</div>
</div>
</div>
</div>
</div>
<div class="status" id="postamble">
<p class="date">Date: 2020-6-4</p>
<p class="author">Author: gdme1320</p>
<p class="validation"><a href="http://validator.w3.org/check?uri=referer">Validate</a></p>
</div>
